package dao.impl;

import common.model.Entity.Student;
import common.model.Entity.User;
import dao.StudentDao;
import common.util.DBUtil;
import dao.UserDao;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.List;
import java.util.Optional;

/**
 * 学生数据访问实现类
 * 实现StudentDao接口，负责Student实体的数据库操作
 */
public class StudentDaoImpl implements StudentDao {

    private static final Logger logger = LoggerFactory.getLogger(StudentDaoImpl.class);
    private final QueryRunner queryRunner = new QueryRunner();
    private UserDao userDao = new UserDaoImpl();

    /**
     * 根据ID查询学生
     * @param id 学生ID
     * @return Optional<Student>
     */
    @Override
    public Optional<Student> findById(Long id) {
        String sql = """
                SELECT\s
                    s.stu_id AS Student_id,
                    s.age AS Age,
                    s.school_year AS School_year,
                    s.class_id AS Class_id,
                    s.college AS College,
                    s.emergency_number AS Emergency_number,
                    s.family_number AS Family_number,
                    s.semester AS Mysemester
                FROM t_student s
                WHERE s.id = ?;
                """;
        try (Connection conn = DBUtil.getConnection()) {
            Student stu = queryRunner.query(conn, sql, new BeanHandler<>(Student.class), id);
            User us = userDao.findById(id).get();
            stu.fillinfo(us);
            return Optional.ofNullable(stu);
        } catch (SQLException e) {
            logger.error("根据学号查询学生失败: id={}", id, e);
            return Optional.empty();
        }
    }

    /**
     * 查询所有学生
     * @return List<Student>
     */
    @Override
    public List<Student> findAll() {
        String sql = """
                SELECT\s
                s.stu_id AS Student_id,
                s.age AS Age,
                s.school_year AS School_year,
                s.class_id AS Class_id,
                s.college AS College,
                s.emergency_number AS Emergency_number,
                s.family_number AS Family_number,
                s.semester AS Mysemester,
                u.real_name AS realName,
                u.gender AS Gender,
                u.phone AS Phone,
                u.email AS Email,
                u.avatar AS Avatar
                FROM t_student s
                INNER JOIN t_user u ON s.id = u.id
                WHERE u.is_deleted = 0;
                """;
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(Student.class));
        } catch (SQLException e) {
            logger.error("查询所有学生失败", e);
            return List.of();
        }
    }

    /**
     * 保存学生信息
     * @param Stu Student对象
     * @return 是否成功
     */
    @Override
    public boolean save(Student Stu) {
        String insertStuSql = "INSERT INTO t_student " +
                "(stu_id, age, school_year, class_id, college, emergency_number, family_number, semester, id) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection()) {
            conn.setAutoCommit(false); // 开启事务
            User us = userDao.findByUsername(Stu.getStudent_id()).get();
            try (PreparedStatement ps = conn.prepareStatement(insertStuSql)) {
                setStuSql(Stu, ps, us);
                int affectedRows = ps.executeUpdate();
                if (affectedRows == 0) {
                    conn.rollback();
                    throw new SQLException("创建学籍失败，未插入任何行。");
                }
            }
            conn.commit();
            return true;
        } catch (SQLException e) {
            logger.error("保存用户失败: {}", Stu, e);
            return false;
        }
    }

    /**
     * 更新学生信息
     * @param Stu Student对象
     * @return 是否成功
     */
    @Override
    public boolean update(Student Stu) {
        String updateStuSql = "UPDATE t_student SET " +
                "stu_id = ?, " +
                "age = ?, " +
                "school_year = ?, " +
                "class_id = ?, " +
                "college = ?, " +
                "emergency_number = ?, " +
                "family_number = ?, " +
                "semester = ? ," +
                "id = ? " +
                "WHERE id = ?";

        try (Connection conn = DBUtil.getConnection()) {
            conn.setAutoCommit(false);
            User us = userDao.findByUsername(Stu.getStudent_id()).get();
            try (PreparedStatement ps = conn.prepareStatement(updateStuSql)) {
                setStuSql(Stu, ps, us);
                ps.setLong(10, us.getId());
                int rows = ps.executeUpdate();
                if (rows == 0) {
                    conn.rollback();
                    throw new SQLException("更新用户失败，未找到用户ID=" + Stu.getId());
                }
            }
            conn.commit();
            return true;
        } catch (SQLException e) {
            logger.error("更新用户失败: {}", Stu, e);
            return false;
        }
    }

    /**
     * 根据ID删除学生
     * @param userId 用户ID
     * @return 是否成功
     */
    @Override
    public boolean deleteById(Long userId) {
        String deleteStudentSql = "DELETE FROM t_student WHERE id = ?";
        try (Connection conn = DBUtil.getConnection()) {
            conn.setAutoCommit(false);
            int affectedRows = queryRunner.update(conn, deleteStudentSql, userId);
            conn.commit();
            return affectedRows > 0;
        } catch (SQLException e) {
            logger.error("删除学生失败: userId={}", userId, e);
            return false;
        }
    }

    /**
     * 设置PreparedStatement参数
     */
    private void setStuSql(Student stu, PreparedStatement ps, User us) throws SQLException {
        ps.setString(1, stu.getStudent_id());
        ps.setInt(2, stu.getAge());
        ps.setString(3, stu.getSchool_year());
        ps.setString(4, stu.getClass_id());
        ps.setString(5, stu.getCollege());
        ps.setString(6, stu.getEmergency_number());
        ps.setInt(7, stu.getFamily_number());
        ps.setString(8, stu.getMysemester());
        ps.setLong(9, us.getId());
    }

    /**
     * 统计学生数量
     * @return 学生总数
     */
    @Override
    public long count() {
        String sql = "SELECT COUNT(*) FROM t_student";
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new ScalarHandler<>());
        } catch (SQLException e) {
            logger.error("统计用户数量失败", e);
            return 0;
        }
    }
}
